package com.example.export;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import javax.swing.*;
import javax.swing.text.JTextComponent;
import javax.swing.text.TableView;
import java.awt.*;
import java.awt.Color;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.*;
import java.util.List;

public class ExportExcel {

    public static void main(String[] args) throws Exception {
        JFrame jframe = new JFrame();
        jframe.setSize(500,500);
        jframe.setLayout(new BorderLayout());
        JPanel panel = new JPanel();
        panel.setBackground(Color.white);
        JTextField uploadText = new JTextField(15);
        JButton uploadButton=new JButton ("上传文件");
        uploadButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                JFileChooser fc = new JFileChooser();
                fc.setDialogTitle("请选择要上传的文件...");
                fc.setApproveButtonText("确定");
                fc.setFileSelectionMode(JFileChooser.FILES_ONLY);
                if (JFileChooser.APPROVE_OPTION == fc.showOpenDialog(panel)) {
                    uploadText.setText(fc.getSelectedFile().getPath());
                }
            }
        });
        panel.add(uploadButton);
        panel.add(uploadText);
        JButton exportButton=new JButton ("导出目录");
        JTextField exportText = new JTextField(15);
        exportButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                JFileChooser fc = new JFileChooser();
                fc.setDialogTitle("请选择要导出的目录");
                fc.setApproveButtonText("确定");
                fc.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
                if (JFileChooser.APPROVE_OPTION == fc.showOpenDialog(panel)) {
                    exportText.setText(fc.getSelectedFile().getPath());
                }
            }
        });
        panel.add(exportButton);
        panel.add(exportText);
        JButton transferButton = new JButton("转换");
        panel.add(transferButton);
        transferButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                if(uploadText.getText()==null || uploadText.getText().trim().length() == 0){
                    JOptionPane.showMessageDialog(transferButton,"请选择文件");
                    return;
                }

                if(exportText.getText()==null || exportText.getText().trim().length() == 0){
                    JOptionPane.showMessageDialog(transferButton,"请指定导出目录");
                    return;
                }
                try {
                    readExcel(uploadText.getText(),exportText.getText());
                    JOptionPane.showMessageDialog(transferButton,"转换成功");
                } catch (Exception ex) {
                    ex.printStackTrace();
                    JOptionPane.showMessageDialog(transferButton,"转换失败！");
                }
            }
        });
        jframe.add(panel);
        jframe.setBounds(300,200,600,200);
        //jframe.setBackground(Color.green);
        //jframe.setLocationRelativeTo(null);
        jframe.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);//关闭窗口，退出程序
        jframe.setVisible(true);
    }

    protected static void readExcel(String filePath,String exportPath) throws Exception{

        InputStream is = new FileInputStream(filePath);
        Workbook workbook = WorkbookFactory.create(is);
        Sheet sheet = workbook.getSheetAt(0);
        List<String> orgList = new ArrayList<>();
        List<String> titleList = new ArrayList<>();//存储表头
        Map<String,String[]> temp = new HashMap<>();
        List<String[]> dataList = new ArrayList<>();
        Row topRow = sheet.getRow(0);
        Cell topRowCell = topRow.getCell(0);
        titleList.add(topRowCell.getStringCellValue());
        for(int i=1;i<= sheet.getLastRowNum();i++){
            Row row = sheet.getRow(i);
            Cell cell = row.getCell(0);
            orgList.add(cell.getStringCellValue());
        }
        for(int i = 0; i<workbook.getNumberOfSheets();i++){
            Sheet sheet1 = workbook.getSheetAt(i);
            Row firstRow = sheet1.getRow(0);
            Cell firstCell = firstRow.getCell(1);
            System.out.println(firstCell.getNumericCellValue());
            titleList.add(firstCell.getStringCellValue());
            System.out.println("name:"+sheet1.getSheetName()+",num:"+sheet1.getLastRowNum());

            for(int j = 1;j<= sheet1.getLastRowNum();j++){
                Row row = sheet1.getRow(j);
                Cell org = row.getCell(0);
                Cell count = row.getCell(1);
                if(!temp.containsKey(org.getStringCellValue())){
                    temp.put(org.getStringCellValue(),new String[workbook.getNumberOfSheets()+1]);
                }
                System.out.println(j+":"+org+":"+count);
                temp.get(org.getStringCellValue())[i+1]=String.valueOf((long)(count.getNumericCellValue()));

            }
        }

        for(String org:orgList){
            if(temp.containsKey(org)){
                String[] rowData = temp.get(org);
                rowData[0] = org;
                dataList.add(rowData);
            }
        }

        setExcel(titleList,dataList,exportPath);
    }

    private static void setExcel(List<String> titleList,List<String[]> dataList,String exportPath) throws Exception {
        Workbook wb=new HSSFWorkbook();
        // 导出xls
        Sheet sheet = wb.createSheet("统计汇总");
        // 居中单元格样式
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中 、居左Left、居右RIGHT
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中、居上TOP、居下button
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setWrapText(true);
        // 表头
        Row headerRow = sheet.createRow(0);
        headerRow.setHeight((short) (200 * 2.5));
        for(int i = 0;i< titleList.size();i++){
            Cell cell = headerRow.createCell(i);
            cell.setCellStyle(style);
            cell.setCellValue(titleList.get(i));
        }

        for(int i = 0;i<dataList.size();i++){
            Row row = sheet.createRow(i+1);
            String[] rowData = dataList.get(i);
            for(int j = 0;j<titleList.size();j++){
                Cell cell = row.createCell(j);
                cell.setCellStyle(style);
                if(j>0){
                    try {
                        if(rowData[j]==null){
                            cell.setCellValue(0);
                        }else {
                            cell.setCellValue(Long.valueOf(rowData[j]));
                        }
                    }catch (Exception e){
                    }
                }else {
                    cell.setCellValue(rowData[j]);
                }
            }
        }

        sheet.autoSizeColumn(0);
        FileOutputStream file = new FileOutputStream(exportPath+ File.separator+"OA统计汇总.xls");
        wb.write(file);
        file.close();
        wb.close();
    }
}
